package DBLayer;

import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * @author Gruppe 2
 *
 */
public class DBProduct implements IFDBProduct {

	private Connection con;

	public DBProduct() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public void insertProduct(Product p) throws SQLException {
		try {
			p.setProductId(GetMax.getMaxId("SELECT max(id)+1 FROM Product"));

			DbConnection.startTransaction();

			String query = "INSERT into Product(Id, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, productType, supplierId) "
					+ "Values (?,?,?,?,?,?,?,?,?)";

			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, p.getProductId());
			ps.setString(2, p.getName());
			ps.setDouble(3, p.getPurchasePrice());
			ps.setDouble(4, p.getSalesPrice());
			ps.setDouble(5, p.getRentPrice());
			ps.setString(6, p.getCountryOfOrigin());
			ps.setInt(7, p.getMinStock());
			if (p instanceof GunReplica) {
				ps.setString(8, "Gun");
			}
			if (p instanceof Clothing) {
				ps.setString(8, "Cloth");
			}
			if (p instanceof Equipment) {
				ps.setString(8, "Equip");
			}
			ps.setInt(9, p.getSupplier().getId());
			ps.executeUpdate();

			if (p instanceof GunReplica) {
				String query1 = "INSERT into GunReplicas(productId, fabric, calibre) "
						+ "Values (?,?,?)";
				GunReplica g = (GunReplica) p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setInt(1, p.getProductId());
				ps1.setString(2, g.getFabric());
				ps1.setString(3, g.getCalibre());
				ps1.executeUpdate();

			}

			if (p instanceof Clothing) {
				String query1 = "INSERT into Clothing(productId, size, color ) "
						+ "Values (?,?,?)";
				Clothing c = (Clothing) p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setInt(1, p.getProductId());
				ps1.setString(2, c.getSize());
				ps1.setString(3, c.getColor());
				ps1.executeUpdate();

			}

			if (p instanceof Equipment) {
				String query1 = "INSERT into Equipment(productId, type, description ) "
						+ "Values (?,?,?)";
				Equipment e = (Equipment) p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setInt(1, p.getProductId());
				ps1.setString(2, e.getType());
				ps1.setString(3, e.getDescription());
				ps1.executeUpdate();

			}

			DbConnection.commitTransaction();

		} catch (SQLException e) {
			DbConnection.rollbackTransaction();
			throw e;
		}

	}

	@Override
	public Product selectSingleProduct(int id) throws SQLException {
		Product p = null;
		String query = "SELECT productType, id, name, purchasePrice, salesPrice, "
				+ "rentPrice, countryOfOrigin, minStock, supplierId "
				+ "FROM Product WHERE id=?";
		ResultSet result = null;
		try {
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, id);
			result = ps.executeQuery();
			if (result.next()) {
				p = buildProduct(result);
			}
		} catch (SQLException e) {
			throw e;
		}
		// TODO Auto-generated method stub
		return p;
	}

	@Override
	public void updateProduct(Product p) throws SQLException {
		// TODO Auto-generated method stub
		try {
			
			DbConnection.startTransaction();
	
			String query="UPDATE Product SET name=?, purchasePrice=?, salesPrice=?, rentPrice=?, countryOfOrigin=?, minStock=?, productType=?, supplierId=?"+
			 	  
			          " WHERE id=?";
		
		          
			PreparedStatement ps = con.prepareStatement(query);
			ps.setString(1, p.getName());
			ps.setDouble(2, p.getPurchasePrice());
			ps.setDouble(3, p.getSalesPrice());
			ps.setDouble(4, p.getRentPrice());
			ps.setString(5, p.getCountryOfOrigin());
			ps.setInt(6, p.getMinStock());
			if  (p instanceof GunReplica) {
				ps.setString(7, "Gun");
			}
			if  (p instanceof Clothing) {
				ps.setString(7, "Cloth");
			}
			if  (p instanceof Equipment) {
				ps.setString(7, "Equip");
			}
			ps.setInt(8, p.getSupplier().getId());
			ps.setInt(9, p.getProductId());
			ps.executeUpdate();
			
	       
				 		
		
			if(p instanceof GunReplica)
			{
				String query1 = "UPDATE GunReplicas SET fabric=?, calibre=? WHERE productId=?";
						
				GunReplica g = (GunReplica)p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setString(1, g.getFabric());
				ps1.setString(2, g.getCalibre());
				ps1.setInt(3, g.getProductId());
				ps1.executeUpdate();
				
			}
			
			if(p instanceof Clothing)
			{
				String query1 = "UPDATE Clothing SET size=?, color=? WHERE productId=?";
				Clothing c = (Clothing)p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setString(1, c.getSize());
				ps1.setString(2, c.getColor());
				ps1.setInt(3, c.getProductId());
				ps1.executeUpdate();
				
			} 
			
			if(p instanceof Equipment)
			{
				String query1 = "UPDATE Equipment SET type=?, description=? WHERE productId=?";
				Equipment e = (Equipment)p;
				PreparedStatement ps1 = con.prepareStatement(query1);
				ps1.setString(1, e.getType());
				ps1.setString(2, e.getDescription());
				ps1.setInt(3, e.getProductId());
				ps1.executeUpdate();
				
			
			}
			
			DbConnection.commitTransaction();
		}
		catch (SQLException e) {
			System.out.println("SQL Error");
			System.out.println(e.getMessage());
			DbConnection.rollbackTransaction();
			throw e;

		
		}
		
	}
	

	@Override
	public void deleteProduct(Product p) throws SQLException {
		try {
			
			String query="DELETE FROM Product WHERE id=?";
		
		          
			PreparedStatement ps = con.prepareStatement(query);
			ps.setInt(1, p.getProductId());
			ps.executeUpdate();
		}
		catch(SQLException e){
			throw e;
			
		}
		
	}

	private Product buildProduct(ResultSet result) throws SQLException {
		Product p = null;
		try {
			String productType = result.getString("productType");
			switch (productType) {
			case "Gun":
				p = buildGunReplica(result);
				break;
			case "Cloth":
				p = buildClothing(result);
				break;
			case "Equip":
				p = buildEquipment(result);
				break;
			}
		} catch (SQLException e) {
			throw e;
		}
		return p;
	}

	private GunReplica buildGunReplica(ResultSet result) throws SQLException {
		GunReplica gun = new GunReplica();
		try {
			gun.setProductId(result.getInt("id"));
			gun.setName(result.getNString("name"));
			gun.setPurchasePrice(result.getDouble("purchasePrice"));
			gun.setSalesPrice(result.getDouble("salesPrice"));
			gun.setRentPrice(result.getDouble("rentPrice"));
			gun.setCountryOfOrigin(result.getNString("countryOfOrigin"));
			gun.setMinStock(result.getInt("minStock"));
			gun.setSupplier(new Supplier());
			gun.getSupplier().setId(result.getInt("supplierId"));
			PreparedStatement ps = con
					.prepareStatement("SELECT fabric,calibre FROM GunReplicas WHERE productid=?");
			ps.setInt(1, gun.getProductId());
			ResultSet sub = ps.executeQuery();
			if (sub.next()) {
				gun.setFabric(sub.getNString("fabric"));
				gun.setCalibre(sub.getNString("calibre"));
			}
			// Supplier needs update!
		} catch (SQLException e) {
			
			throw e;
		}
		return gun;
	}

	private Clothing buildClothing(ResultSet result) throws SQLException {
		Clothing cloth = new Clothing();
		try {
			cloth.setProductId(result.getInt("id"));
			cloth.setName(result.getNString("name"));
			cloth.setPurchasePrice(result.getDouble("purchasePrice"));
			cloth.setSalesPrice(result.getDouble("salesPrice"));
			cloth.setRentPrice(result.getDouble("rentPrice"));
			cloth.setCountryOfOrigin(result.getNString("countryOfOrigin"));
			cloth.setMinStock(result.getInt("minStock"));
			cloth.setSupplier(new Supplier());
			cloth.getSupplier().setId(result.getInt("supplierId"));
			PreparedStatement ps = con
					.prepareStatement("SELECT size,color FROM Clothing WHERE productid=?");
			ps.setInt(1, cloth.getProductId());
			ResultSet sub = ps.executeQuery();
			if (sub.next()) {
				cloth.setSize(sub.getString("size"));
				cloth.setColor(sub.getString("color"));
			}
			// Supplier needs update!
		} catch (SQLException e) {
			throw e;
		}

		return cloth;
	}

	private Equipment buildEquipment(ResultSet result) throws SQLException {
		Equipment equip = new Equipment();
		try {
			equip.setProductId(result.getInt("id"));
			equip.setName(result.getNString("name"));
			equip.setPurchasePrice(result.getDouble("purchasePrice"));
			equip.setSalesPrice(result.getDouble("salesPrice"));
			equip.setRentPrice(result.getDouble("rentPrice"));
			equip.setCountryOfOrigin(result.getNString("countryOfOrigin"));
			equip.setMinStock(result.getInt("minStock"));
			equip.setSupplier(new Supplier());
			equip.getSupplier().setId(result.getInt("supplierId"));
			PreparedStatement ps = con
					.prepareStatement("SELECT type,description FROM Equipment WHERE productid=?");
			ps.setInt(1, equip.getProductId());
			ResultSet sub = ps.executeQuery();
			if (sub.next()) {
				equip.setType(sub.getString("type"));
				equip.setDescription(sub.getString("description"));
			}
			// Supplier needs update!
		} catch (SQLException e) {

			throw e;
		}
		return equip;
	}

	@Override
	public ArrayList<Product> selectAllProduct() throws SQLException {

		ArrayList<Product> arrayListProduct = new ArrayList<Product>();
		
		Product p = null;
		String query = "SELECT productType, id, name, purchasePrice, salesPrice, "
				+ "rentPrice, countryOfOrigin, minStock, supplierId "
				+ "FROM Product";
		ResultSet result = null;
		try {
			PreparedStatement ps = con.prepareStatement(query);
			result = ps.executeQuery();
			while(result.next()) {
				p = buildProduct(result);
				arrayListProduct.add(p);
			}
		} catch (SQLException e) {
			throw e;
		}
		

		return arrayListProduct;
	}

}
